﻿using System;
using System.Collections.Generic;
using System.Text;
using QuanLyKho.Common;
using System.Data;
using System.Data.SqlClient;
using System.Xml;

namespace QuanLyKho.DataAccess
{
    public class clsDataAccessListMaterial
    {

        private SqlConnection conn = new clsConnect().GetSqlConnection();
        private SqlCommand cmd = null;
        private SqlDataReader dr = null;
        private StringBuilder sBuilder = null;

        #region iAction ListMaterial

        public int AddNew(object objParam)
        {
            try
            {
                int status = 0;
                conn.Open();
                clsListMaterial obj = (clsListMaterial)objParam;
                sBuilder = new StringBuilder("splistMaterial_Insert");
                cmd = new SqlCommand(sBuilder.ToString(), conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Code", obj.Code);
                cmd.Parameters.AddWithValue("@Name", obj.Name);
                cmd.Parameters.AddWithValue("@Des", obj.Des);
                cmd.Parameters.AddWithValue("@Unit", obj.Unit);
                cmd.Parameters.AddWithValue("@Specification", obj.Specification);
                cmd.Parameters.AddWithValue("@Category", obj.Category);
                cmd.Parameters.AddWithValue("@IDSupplier", obj.IDSupplier);

                status = cmd.ExecuteNonQuery();
                conn.Close();
                return status;
            }
            catch (Exception ex)
            { throw ex; }
            finally
            { conn.Close(); }
        }

        public int Update(object objParam)
        {
            try
            {
                int status = 0;
                conn.Open();
                clsListMaterial obj = (clsListMaterial)objParam;
                sBuilder = new StringBuilder("splistMaterial_Update");
                cmd = new SqlCommand(sBuilder.ToString(), conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@ID", obj.ID);
                cmd.Parameters.AddWithValue("@Code", obj.Code);
                cmd.Parameters.AddWithValue("@Name", obj.Name);
                cmd.Parameters.AddWithValue("@Des", obj.Des);
                cmd.Parameters.AddWithValue("@Unit", obj.Unit);
                cmd.Parameters.AddWithValue("@Specification", obj.Specification);
                cmd.Parameters.AddWithValue("@Category", obj.Category);
                cmd.Parameters.AddWithValue("@IDSupplier", obj.IDSupplier);
                status = cmd.ExecuteNonQuery();
                conn.Close();
                return status;
            }
            catch (Exception ex)
            { throw ex; }
            finally
            { conn.Close(); }
        }

        public int Delete(long id)
        {
            try
            {
                int status = 0;
                conn.Open();
                sBuilder = new StringBuilder("splistMaterial_Delete");
                cmd = new SqlCommand(sBuilder.ToString(), conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@ID", id);
                status = cmd.ExecuteNonQuery();
                conn.Close();
                return status;
            }
            catch (Exception ex)
            { throw ex; }
            finally
            { conn.Close(); }
        }

        #endregion



        //lay theo ID

        public List<clsListMaterial> SelectOneWithId(long id)
        {
            try
            {
                List<clsListMaterial> list = new List<clsListMaterial>();
                sBuilder = new StringBuilder("splistMaterial_SelectByID");
                conn.Open();
                cmd = new SqlCommand(sBuilder.ToString(), conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@ID", id);
                dr = cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        clsListMaterial obj = new clsListMaterial();
                        if (dr["ID"] != DBNull.Value)
                            obj.ID = Convert.ToInt64(dr["ID"]);
                        if (dr["Code"] != DBNull.Value)
                            obj.Code = Convert.ToString(dr["Code"]);
                        if (dr["Name"] != DBNull.Value)
                            obj.Name = Convert.ToString(dr["Name"]);
                        if (dr["Des"] != DBNull.Value)
                            obj.Des = Convert.ToString(dr["Des"]);
                        if (dr["Unit"] != DBNull.Value)
                            obj.Unit = Convert.ToString(dr["Unit"]);
                        if (dr["Specification"] != DBNull.Value)
                            obj.Specification = Convert.ToString(dr["Specification"]);
                        if (dr["Category"] != DBNull.Value)
                            obj.Category = Convert.ToString(dr["Category"]);
                        if (dr["IDSupplier"] != DBNull.Value)
                            obj.IDSupplier = Convert.ToInt64(dr["IDSupplier"]);
                        list.Add(obj);
                    }
                }
                conn.Close();
                return list;
            }
            catch (Exception ex)
            { throw ex; }
            finally
            { conn.Close(); }
        }

        //select by name

        public List<clsListMaterial> SelectAllWithName(string Name)
        {
            try
            {
                List<clsListMaterial> list = new List<clsListMaterial>();
                sBuilder = new StringBuilder("splistMaterial_SelectByName");
                conn.Open();
                cmd = new SqlCommand(sBuilder.ToString(), conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Name", Name);
                dr = cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        clsListMaterial obj = new clsListMaterial();
                        if (dr["ID"] != DBNull.Value)
                            obj.ID = Convert.ToInt64(dr["ID"]);
                        if (dr["Code"] != DBNull.Value)
                            obj.Code = Convert.ToString(dr["Code"]);
                        if (dr["Name"] != DBNull.Value)
                            obj.Name = Convert.ToString(dr["Name"]);
                        if (dr["Des"] != DBNull.Value)
                            obj.Des = Convert.ToString(dr["Des"]);
                        if (dr["Unit"] != DBNull.Value)
                            obj.Unit = Convert.ToString(dr["Unit"]);
                        if (dr["Specification"] != DBNull.Value)
                            obj.Specification = Convert.ToString(dr["Specification"]);
                        if (dr["Category"] != DBNull.Value)
                            obj.Category = Convert.ToString(dr["Category"]);
                        if (dr["IDSupplier"] != DBNull.Value)
                            obj.IDSupplier = Convert.ToInt64(dr["IDSupplier"]);

                        list.Add(obj);
                    }
                }
                conn.Close();
                return list;
            }
            catch (Exception ex)
            { throw ex; }
            finally
            { conn.Close(); }
        }

        //doc xml

        public List<clsListMaterial> ReadXmlFile(string FileName)
        {
            List<clsListMaterial> ListMaterial = null;
            try
            {
                XmlDocument _doc = new XmlDocument();

                _doc.Load(FileName);
              
                ListMaterial = new List<clsListMaterial>();
                foreach (XmlNode node in _doc.GetElementsByTagName("listMaterial"))
                {
                    
                    clsListMaterial obj = new clsListMaterial();

                    XmlNode ID = node.SelectSingleNode("ID");
                    XmlNode Code = node.SelectSingleNode("Code");
                    XmlNode Name = node.SelectSingleNode("Name");
                    XmlNode Des = node.SelectSingleNode("Des");
                    XmlNode Unit = node.SelectSingleNode("Unit");
                    XmlNode Specification = node.SelectSingleNode("Specification");
                    XmlNode Category = node.SelectSingleNode("Category");
                    XmlNode IDSupplier = node.SelectSingleNode("IDSupplier");
                    obj.ID = Convert.ToInt64(ID.InnerText);
                    obj.Code = Convert.ToString(Code.InnerText);
                    obj.Name = Convert.ToString(Name.InnerText);
                    obj.Des = Convert.ToString(Des.InnerText);
                    obj.Unit = Convert.ToString(Unit.InnerText);
                    obj.Specification = Convert.ToString(Specification.InnerText);
                    obj.Category = Convert.ToString(Category.InnerText);
                    obj.IDSupplier = Convert.ToInt64(IDSupplier.InnerText);

                    ListMaterial.Add(obj);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ListMaterial;
        }

    }
}
